﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Threading;
using CVSA.Suporte.Sinacor.BMFBovespa.Entidades;
using System.Configuration;
using CVSA.Suporte.Sinacor.BMFBovespa.Negocio;


namespace GerarXMLOdbcRemedy
{
    public partial class frmMain : Form
    {
       
        public frmMain()
        {
            InitializeComponent();
        }
                
        private void Form1_Load(object sender, EventArgs e)
        {
            Remedy();
        }

        private void Remedy()
        {
            //Remedy remedy = new Remedy();
            //DataTable odt = remedy.ObterDadosOciosos();
            //remedy.AtualizarChamadosOciosos(odt);
            
            try
            {
                dg.DataSource = null;
                //lblCountAnterior.Text = "Leitura Anterior: " + lblCountAtual.Text;
                //string dtInicio = " AND Submit_Date >= {ts '2010-01-01 00:00:00'}";
                string p = string.Empty;

                OdbcConnection cmd = new OdbcConnection("Dsn=AR System ODBC Data Source;arserver=arscorp1;arserverport=50001;UID=" + ConfigurationManager.AppSettings["remedyUserName"] + ";PWD=" + Tools.Decrypt(ConfigurationManager.AppSettings["remedyPassword"], true) + ";aruseunderscores=1;arnamereplace=1;server=NotTheServer");
                OdbcDataAdapter da = new OdbcDataAdapter(p, cmd);

                #region "Comentado"

                //                #region CVSA Mês
//                p = @"SELECT ""HPD_Help_Desk"".""Submit_Date"", ""HPD_Help_Desk"".""Status"", ""HPD_Help_Desk"".""Assigned_Group"", ""HPD_Help_Desk"".""Assignee"", ""HPD_Help_Desk"".""Incident_Number"", ""HPD_Help_Desk"".""Closed_Date"" ,
//                            ""HPD_Help_Desk"".""Configuration_Item"",
//                            ""HPD_Help_Desk"".""Last_Resolved_Date"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""PRIORITY"",
//                            ""HPD_Help_Desk"".""COMPANY""
//                            FROM   ""HPD_Help_Desk"" ""HPD_Help_Desk"" WHERE  (""HPD_Help_Desk"".""Assigned_Group"" LIKE 'DO-CVSA SUPORTE%')" + dtInicio;

//                da = new OdbcDataAdapter(p, cmd);
//                DataSet dsCVSAMes = new DataSet();
//                da.Fill(dsCVSAMes);

//                foreach (DataRow row in dsCVSAMes.Tables[0].Rows)
//                {
//                    //if (row["Configuration_Item"].ToString().Split(';').Length > 2)
//                    //{
//                        row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[0];
//                    //}
//                    //else
//                    //{
//                    //    row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[0];
//                    //}
//                }

//                dg.DataSource = dsCVSAMes.Tables[0];
//                dsCVSAMes.WriteXml(@"c:\slmCVSAMes.xsd");


//                #endregion

//                #region CVSA Total
//                p = @"SELECT ""HPD_Help_Desk"".""Submit_Date"", ""HPD_Help_Desk"".""Status"", ""HPD_Help_Desk"".""Assigned_Group"", ""HPD_Help_Desk"".""Assignee"", ""HPD_Help_Desk"".""Incident_Number"", ""HPD_Help_Desk"".""Closed_Date"" ,
//                            ""HPD_Help_Desk"".""Configuration_Item"",
//                            ""HPD_Help_Desk"".""Last_Resolved_Date"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""PRIORITY"",
//                            ""HPD_Help_Desk"".""COMPANY""
//                            FROM   ""HPD_Help_Desk"" ""HPD_Help_Desk"" WHERE  (""HPD_Help_Desk"".""Assigned_Group"" LIKE 'DO-CVSA SUPORTE%')";

//                da = new OdbcDataAdapter(p, cmd);
//                DataSet dsCVSATotal = new DataSet();
//                da.Fill(dsCVSATotal);

//                foreach (DataRow row in dsCVSATotal.Tables[0].Rows)
//                {
//                    if (row["Configuration_Item"].ToString().Split(';').Length > 2)
//                    {
//                        row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[1];
//                    }
//                    else
//                    {
//                        row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[0];
//                    }
//                }

//                dsCVSATotal.WriteXml(@"c:\slmCVSATotal.xsd");
//                #endregion

//                #region CSSA Mês
//                p = @"SELECT  ""HPD_Help_Desk"".""Submit_Date"", ""HPD_Help_Desk"".""Status"", ""HPD_Help_Desk"".""Assigned_Group"", ""HPD_Help_Desk"".""Assignee"", ""HPD_Help_Desk"".""Incident_Number"", ""HPD_Help_Desk"".""Closed_Date"" ,
//                    ""HPD_Help_Desk"".""Configuration_Item"",
//                            ""HPD_Help_Desk"".""Last_Resolved_Date"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""PRIORITY"",
//                            ""HPD_Help_Desk"".""COMPANY""
//                    FROM   ""HPD_Help_Desk"" ""HPD_Help_Desk"" WHERE  (""HPD_Help_Desk"".""Assigned_Group"" LIKE 'DO-CSSA SUPORTE SINACOR - NEGOCIO%')" + dtInicio;

//                da = new OdbcDataAdapter(p, cmd);
//                DataSet dsCSSAMes = new DataSet();
//                da.Fill(dsCSSAMes);

//                foreach (DataRow row in dsCSSAMes.Tables[0].Rows)
//                {
//                    if (row["Configuration_Item"].ToString().Split(';').Length > 2)
//                    {
//                        row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[1];
//                    }
//                    else
//                    {
//                        row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[0];
//                    }
//                }

//                dsCSSAMes.WriteXml(@"c:\slmCSSAMes.xsd");
//                #endregion

//                #region CSSA Total
//                p = @"SELECT  ""HPD_Help_Desk"".""Submit_Date"", ""HPD_Help_Desk"".""Status"", ""HPD_Help_Desk"".""Assigned_Group"", ""HPD_Help_Desk"".""Assignee"", ""HPD_Help_Desk"".""Incident_Number"", ""HPD_Help_Desk"".""Closed_Date"" ,
//                    ""HPD_Help_Desk"".""Configuration_Item"",
//                            ""HPD_Help_Desk"".""Last_Resolved_Date"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_1"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_2"",
//                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_3"",
//                            ""HPD_Help_Desk"".""PRIORITY"",
//                            ""HPD_Help_Desk"".""COMPANY""
//                    FROM   ""HPD_Help_Desk"" ""HPD_Help_Desk"" WHERE  (""HPD_Help_Desk"".""Assigned_Group"" LIKE 'DO-CSSA SUPORTE SINACOR - NEGOCIO%')";

//                da = new OdbcDataAdapter(p, cmd);
//                DataSet dsCSSATotal = new DataSet();
//                da.Fill(dsCSSATotal);

//                foreach (DataRow row in dsCSSATotal.Tables[0].Rows)
//                {
//                    if (row["Configuration_Item"].ToString().Split(';').Length > 2)
//                    {
//                        row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[1];
//                    }
//                    else
//                    {
//                        row["Configuration_Item"] = row["Configuration_Item"].ToString().Split(';')[0];
//                    }
//                }

//                dsCSSATotal.WriteXml(@"c:\slmCSSATotal.xsd");
                //                #endregion
                #endregion

                #region Total
                p = @"SELECT  ""HPD_Help_Desk"".""Submit_Date"", ""HPD_Help_Desk"".""Status"", ""HPD_Help_Desk"".""Assigned_Group"", ""HPD_Help_Desk"".""Assignee"", ""HPD_Help_Desk"".""Incident_Number"", ""HPD_Help_Desk"".""Closed_Date"" ,
                    ""HPD_Help_Desk"".""Configuration_Item"",
                            ""HPD_Help_Desk"".""Last_Resolved_Date"",
                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_1"",
                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_2"",
                            ""HPD_Help_Desk"".""CATEGORIZATION_TIER_3"",
                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_1"",
                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_2"",
                            ""HPD_Help_Desk"".""GENERIC_CATEGORIZATION_TIER_3"",
                            ""HPD_Help_Desk"".""PRIORITY"",
                            ""HPD_Help_Desk"".""COMPANY"",
                            ""HPD_Help_Desk"".""DESCRIPTION""
                    FROM   ""HPD_Help_Desk"" ""HPD_Help_Desk"" 
                    WHERE  (""HPD_Help_Desk"".""Assigned_Group"" LIKE 'DO-DSE-SUPORTE%') 
                    AND ""HPD_Help_Desk"".""Submit_Date"" > {ts '2010-01-01 00:00:00'}";

                da = new OdbcDataAdapter(p, cmd);
                DataSet dsTotal = new DataSet();
                da.Fill(dsTotal);
                foreach (DataRow row in dsTotal.Tables[0].Rows)
                {
                    row["configuration_item"] = row["configuration_item"].ToString().Split(';')[0];
                }

                dsTotal.WriteXml((@"c:\slmTotal.xsd"));
                dg.DataSource = dsTotal.Tables[0];
                #endregion

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
       
        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Remedy();
        }
    }
}
